package com.miao.util.poi;

import com.miao.util.file.FileUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;

/**
 *
 */
public class WorkbookDispacher {

    public static final Integer XLS = 1;
    public static final Integer XLSX = 2;
    private static Logger logger = Logger.getLogger(WorkbookDispacher.class);
    private String dirPath;
    private String fullFileName;
    private String fileName;
    private String extendName;

    // 如果这个WorkbookDispacher是用于打开或生成某个sheet,就要设置这个属性,很方便
    private String currentSheetName;

    private Integer excelType = XLS;


    //文件名池
    private String[] files;         // 所有公式EXCEL文件,包括主文件
    //workbook池
    private Workbook[] wbs;

    // 仿真器池
    // 不能定义成FormulaEvaluator,因为只有HSSFFormulaEvaluator.setupEnvironment()方法,且只接受HSSFFormulaEvaluator参数
    // 强制转换FormulaEvaluator为HSSFFormulaEvaluator,会出cast异常
    private HSSFFormulaEvaluator[] evaluators;   // 所有EXCEL文件的仿真器,包括主文件的

    // 2007仿真器池
    private XSSFFormulaEvaluator[] xlsxEvaluators;

    // 对应的模板的从数据库FAS_TEMPLATE_CELL表查出的CELL列表.
    //private List<YCFasTemplateCell> cellList;

    /**
     * 构造方法1
     *
     * @param fullFileName
     * @param rFiles       第二个参数为关联文件
     * @throws Exception
     * @throws IOException
     */
    public WorkbookDispacher(String fullFileName, String[] rFiles) throws Exception, IOException {
        super();
        this.checkFullFileName(fullFileName);
        creatEnvironment(rFiles, false);

    }

    /**
     * 构造方法2
     * 增加了一个布尔值, 为false时是读本地文件,为true时,是建立新文件
     *
     * @param fullFileName
     * @param rFiles       第二个参数为关联文件
     * @param newFlag      为false时是读本地文件,为true时,是建立新文件
     * @throws Exception
     * @throws IOException
     */
    public WorkbookDispacher(String fullFileName, String[] rFiles, boolean newFlag) throws Exception, IOException {
        super();
        this.checkFullFileName(fullFileName);
        creatEnvironment(rFiles, newFlag);

    }

    /**
     * 构造方法3
     *
     * @param rFiles 第二个参数为关联文件
     * @throws Exception
     * @throws IOException
     */
    public WorkbookDispacher(String dirPath, String fileName, String extendName, String[] rFiles) throws Exception, IOException {
        super();
        this.checkPath_FileName_Extendname(dirPath, fileName, extendName);
        creatEnvironment(rFiles, false);

    }

    /**
     * 构造方法4
     * 增加了一个布尔值, 为false时是读本地文件,为true时,是建立新文件
     *
     * @param rFiles 第二个参数为关联文件
     * @throws Exception
     * @throws IOException
     */
    public WorkbookDispacher(String dirPath, String fileName, String extendName, String[] rFiles, boolean newFlag) throws Exception, IOException {
        super();
        this.checkPath_FileName_Extendname(dirPath, fileName, extendName);
        creatEnvironment(rFiles, newFlag);

    }


    public String getDirPath() {
        return dirPath;
    }

    public void setDirPath(String dirPath) {
        this.dirPath = dirPath;
    }

    public String getFullFileName() {
        return fullFileName;
    }

    public void setFullFileName(String fullFileName) {
        this.fullFileName = fullFileName;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String getExtendName() {
        return extendName;
    }

    public void setExtendName(String extendName) {
        this.extendName = extendName;
    }


    public HSSFFormulaEvaluator[] getEvaluators() {
        return evaluators;
    }

    public void setEvaluators(HSSFFormulaEvaluator[] evaluators) {
        this.evaluators = evaluators;
    }

    public String[] getFiles() {
        return files;
    }

    public void setFiles(String[] files) {
        this.files = files;
    }

/*	public Workbook[] getWbs() {
        return wbs;
	}*/

    public Workbook getWb() {
        return wbs[0];
    }

/*	public void setWbs(Workbook[] wbs) {
		this.wbs = wbs;
	}*/

    public Integer getExcelType() {
        return excelType;
    }

    public void setExcelType(Integer excelType) {
        this.excelType = excelType;
    }

    public String getCurrentSheetName() {
        return currentSheetName;
    }

    public void setCurrentSheetName(String currentSheetName) {
        this.currentSheetName = currentSheetName;
    }

    public FormulaEvaluator getCurrentEvaluator() {
        if (this.evaluators != null && evaluators.length > 0)
            return evaluators[0];
        if (this.xlsxEvaluators != null && xlsxEvaluators.length > 0)
            return xlsxEvaluators[0];
        return null;
    }


/*	public List<YCFasTemplateCell> getCellList() {
		return cellList;
	}

	public void setCellList(List<YCFasTemplateCell> cellList) {
		this.cellList = cellList;
	}*/


    //---------------------------------custom method--------------------------------------//


    public void checkFullFileName(String fullFileName) throws Exception {
        if (StringUtils.isBlank(fullFileName)) {
            throw new Exception("FileName is null!");
        }
        this.fullFileName = fullFileName;
        this.dirPath = FileUtil.getPath(fullFileName);
        this.fileName = FileUtil.getFileName_noExtend(fullFileName);
        this.extendName = FileUtil.getExtendName(fullFileName);

        if (extendName == null) {
            throw new Exception("Extend name must be xls or xlsx!");
        }
        if (extendName.equalsIgnoreCase("xls")) {
            // 仅仅为了占位，不做任何操作。(保持逻辑的完整性，不是xls\xlsx就抛出异常)
            return;
        } else if (extendName.equalsIgnoreCase("xlsx")) {
            this.excelType = XLSX;
            return;
        } else {
            throw new Exception("Extend name must be xls or xlsx!");
        }
    }

    public void checkPath_FileName_Extendname(String dirPath, String fileName, String extendName) throws Exception {
        extendName = extendName.trim().toLowerCase();
        if (StringUtils.isBlank(fileName) || StringUtils.isBlank(extendName))
            throw new Exception("FileName is null!");
        if (extendName.equalsIgnoreCase("xls") || extendName.equalsIgnoreCase("XLS")) {
            this.fileName = fileName;
            this.extendName = extendName;
            this.dirPath = dirPath;
            this.fullFileName = FileUtil.addPathSuffix(dirPath) + fileName + "." + extendName;
        } else if (extendName.equalsIgnoreCase("xlsx") || extendName.equalsIgnoreCase("XLSX")) {
            this.fileName = fileName;
            this.extendName = extendName;
            this.excelType = XLSX;
            this.fullFileName = FileUtil.addPathSuffix(dirPath) + fileName + "." + extendName;
        } else {
            throw new Exception("Extend name must be xls or xlsx!");
        }
    }


    private void creatEnvironment(String[] rFiles, boolean newFlag) throws Exception, IOException {

        // 1.文件名池(不带路径)
        // 主文件和关联文件入文件名池
        if (rFiles != null && rFiles.length > 0) {
            this.files = new String[rFiles.length + 1];            //建池
            files[0] = fileName + "." + extendName;
            for (int i = 0; i < rFiles.length; i++) {
                files[i + 1] = rFiles[i];
            }
        } else {
            this.files = new String[1];                            //建池
            files[0] = fileName + "." + extendName;
        }

        //2.workbook对象池
        wbs = new Workbook[files.length];                        //建池
        // 主文件和关联文件入生成workbook对象入wb池
        for (int i = 0; i < files.length; i++) {
            if (!newFlag) {
                wbs[i] = POIUtil.readExcelFile(this.dirPath, files[i]);
            }
            // 建新workbook句柄,不读本地存在的EXCEL文件,(用于直接从数据库生成EXCEL workbook对象在页面显示)
            //TODO.. new 新句柄方式不支持FORMULA关联EXCEL文件,因为连关联文件都是new出来的,根本就没读
            else {
                // 按说新生成的都应该是2003格式的,但有可能数据库中模板是2007文件导入生成,扩展名是XLSX,所以不能用扩展名判断,而是统一生成2003格式
                this.excelType = XLS;
                wbs[i] = new HSSFWorkbook();
            }
        }

        // 仿真器池
        if (this.excelType == XLS) {
            evaluators = new HSSFFormulaEvaluator[files.length];    //建池
            for (int i = 0; i < files.length; i++) {
                //evaluators[i] = (HSSFFormulaEvaluator) wbs[i].getCreationHelper().createFormulaEvaluator();
                evaluators[i] = new HSSFFormulaEvaluator((HSSFWorkbook) wbs[i]);
            }
        } else {
            xlsxEvaluators = new XSSFFormulaEvaluator[files.length];    //建池
            for (int i = 0; i < files.length; i++) {
                //evaluators[i] = (HSSFFormulaEvaluator) wbs[i].getCreationHelper().createFormulaEvaluator();
                xlsxEvaluators[i] = new XSSFFormulaEvaluator((XSSFWorkbook) wbs[i]);
            }
        }

        logger.debug("WorkbookDispacher对象将" + (!newFlag ? "打开(硬盘上)" : "生成(在内存中)") + files.length + "个文件");
        for (String temp : files)
            logger.debug((!newFlag ? "打开(硬盘上)" : "生成(在内存中)") + "文件:" + temp);

        // 将所有关联的EXCEL文件的模拟器压入同一计算环境
        if (this.excelType == XLS) {
            HSSFFormulaEvaluator.setupEnvironment(files, evaluators);
        } else {
            // 没这个方法
            //XSSFFormulaEvaluator.setupEnvironment
        }
    }


}
